# -*- UTF-8 -*-
import re

import progressbar
import pymysql
import time
import pandas as pd


def create_select_sql(start_date, end_date):
    s1 = "SELECT Tags, AnswerCount FROM questions WHERE  CreationDate >= "
    s2 = " AND CreationDate <= "
    start_date_str = "'" + str(start_date) + "-01'"
    end_date_str = "'" + str(end_date) + "-01'"
    return s1 + start_date_str + s2 + end_date_str


def get_tag_trend(start_month, end_month):
    '''
    获得[start_month, end_month]，标签趋势数目的dataframe，并将其存入start_month.csv中
    :param start_month: string like 2016-01
    :param end_month: string like 2017-01(该月是多余的月份，需要再次处理文件删除掉)
    '''
    db = pymysql.connect("localhost", "root", "123456", "stackoverflow")
    cursor = db.cursor()
    # Calculate running times
    start_time = time.time()

    # Sql
    get_tags_sql = "SELECT * FROM tags"

    cursor.execute(get_tags_sql)
    tags = cursor.fetchall()
    # 'Tags_name':[num1, num2...]
    column = []
    for tag in tags:
        column.append(tag[1])

    # Get month_index [start_month , end_month] as DataFrame row
    row = pd.period_range(start=start_month, end=end_month, freq='M')

    tags_dataframe = pd.DataFrame(data=0, index=row, columns=column)

    pattern = re.compile(r"\<(.*?)\>", re.I | re.X)

    widgets = [progressbar.Percentage(), progressbar.Bar()]
    # months loop
    for i in range(0, row.__len__() - 1):
        print("Start " + str(row[i]))
        select_sql = create_select_sql(row[i], row[i + 1])
        cursor.execute(select_sql)
        question_items = cursor.fetchall()
        bar_max_value = question_items.__len__()
        bar_count = 0
        bar = progressbar.ProgressBar(widgets=widgets, max_value=bar_max_value).start()
        # every month questions loop
        for item in question_items:
            tags_list = pattern.findall(item[0])
            for tag in tags_list:
                tags_dataframe.loc[row[i], tag] += 1

            bar_count += 1
            bar.update(bar_count)
        print("")  # 换行
    print("")
    tags_dataframe.to_csv(start_month + ".csv", sep=',', index=False)

    print("---%s to %s has Succeeded, elapse %s seconds ---" % (start_month, end_month, (time.time() - start_time)))

    db.close()


if __name__ == '__main__':
    lst = ["2009-01", "2010-01", "2011-01", "2012-01"]
    for i in range(lst.__len__()-1):
        get_tag_trend(lst[i], lst[i+1])
